package ICM.server;

import java.sql.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;
import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

/**This class have a thread that wakes up once a day and responsible for:
 * 1) Sending email notification to stage responsible, supervisor and manager when exception occurs.
 * 2) Sending email reminder to stage responsible one day before stage end date.

 */
public class BackgroundProcess extends Thread {
	/**connection to database, this class get this connection from Server.java class*/
	private Connection conn;
	private String MAIL_SERVER = "smtp.gmail.com";
	private String USERNAME = "icmbraude2@gmail.com";
	private String PASSWORD = "ds324GSa";
	private String fromAddress = "icmbraude2@gmail.com";
	private String subject = "Message from ICM system";
	
	public BackgroundProcess(Connection conn){
		this.conn=conn;
	}
	
	/**function that sleep for 24 hours and checks for stage exception*/
	public void run()
	{
		String query,email,firstname,lastname,NameOfStage;
		int requestID;
		ResultSet rs=null,rs1=null;
		Statement stmt,stmt2;

		while(true)
		{
			query="SELECT * FROM stage WHERE Status='Active' AND DATEDIFF(NOW(),DateEndOfStage)=1";
			try {
				stmt = conn.createStatement();
				rs =  stmt.executeQuery(query);
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				while(rs.next())
				{
					requestID=rs.getInt(5);
					NameOfStage=rs.getString(2);
					//get the stage responsible email
					query="SELECT FirstName,LastName,users.Email FROM users WHERE ID='"+rs.getInt(3)+"'";
					stmt2=conn.createStatement();
					rs1=stmt2.executeQuery(query);
					rs1.next();
					firstname=rs1.getString(1);
					lastname=rs1.getString(2);
					email=rs1.getString(3);
					
					SendExceptionEmail(firstname,lastname,email,requestID,NameOfStage);
					
					//get supervisor email
					query="SELECT FirstName,LastName,users.Email FROM users,requests WHERE idRequests='"+rs.getInt(5)+"' AND idSupervisor=ID";
					stmt2=conn.createStatement();
					rs1=stmt2.executeQuery(query);
					rs1.next();
					firstname=rs1.getString(1);
					lastname=rs1.getString(2);
					email=rs1.getString(3);
					
					SendExceptionEmail(firstname,lastname,email,requestID,NameOfStage);
					
					//get manager email
					query="SELECT FirstName,LastName,users.Email FROM users WHERE Role='Manager'";
					stmt2=conn.createStatement();
					rs1=stmt2.executeQuery(query);
					rs1.next();
					firstname=rs1.getString(1);
					lastname=rs1.getString(2);
					email=rs1.getString(3);
					
					SendExceptionEmail(firstname,lastname,email,requestID,NameOfStage);			
					
					query="UPDATE stage SET EndOfStage=NULL,ExceptionStartDate=NOW() WHERE idStage="+rs.getInt(1);
					stmt2 = conn.createStatement();
					stmt2.executeUpdate(query);
					
				}//end of internal while
				
				rs.close();
				
				query="SELECT * FROM stage WHERE Status='Active' AND DATEDIFF(DateEndOfStage,NOW())=1";	
				try {
					stmt = conn.createStatement();
					rs =  stmt.executeQuery(query);
				} catch (Exception e) {
					e.printStackTrace();
				}
					while(rs.next())
					{
						requestID=rs.getInt(5);
						NameOfStage=rs.getString(2);
						query="SELECT FirstName,LastName,users.Email FROM users WHERE ID='"+rs.getInt(3)+"'";
						stmt2=conn.createStatement();
						rs1=stmt2.executeQuery(query);
						rs1.next();
						firstname=rs1.getString(1);
						lastname=rs1.getString(2);
						email=rs1.getString(3);
						
						SendReminderEmail(firstname,lastname,email,requestID,NameOfStage);	
					}
				
				try {                //sleep for 24 hours
					sleep(86400000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			} catch (Exception e) {
				e.printStackTrace();
				try {                //sleep for 24 hours
					sleep(86400000);
				} catch (InterruptedException e1) {
					e1.printStackTrace();
				}
			}
		
		}//end of external while
		
	}
	
	//send an email to user
	public void SendExceptionEmail(String firstname, String lastname, String email, int requestID, String NameOfStage)
	{
		String message;
		message="Hello "+firstname+" "+lastname+", there is an exception in this request: "+requestID+" on stage: "+NameOfStage;
   	try {
	   		Properties properties = System.getProperties();
	   		properties.put("mail.smtps.host", MAIL_SERVER);
	   		properties.put("mail.smtps.auth", "true");
	
	   		Session session = Session.getInstance(properties);
	   		MimeMessage msg = new MimeMessage(session);
	
	   		msg.setFrom(new InternetAddress(fromAddress));
	   		msg.addRecipients(javax.mail.Message.RecipientType.TO, email);
	   		msg.setSubject(subject);
	   		msg.setText(message);
	
	   		Transport tr = session.getTransport("smtps");
	   		tr.connect(MAIL_SERVER, USERNAME, PASSWORD);
	   		tr.sendMessage(msg, msg.getAllRecipients());
	   		tr.close();
   		} catch (AddressException ex) {
   			System.out.println(ex.getMessage());
   		} catch (MessagingException ex) {
   			System.out.println(ex.getMessage());
   		}
   	System.out.println("An email reminder was sent to "+email);
   	// end of sending mail
	}
	
	public void SendReminderEmail(String firstname, String lastname, String email, int requestID, String NameOfStage)
	{
		String message;
		message="Hello "+firstname+" "+lastname+", there is one day left to finish the stage "+NameOfStage+" on this request: "+requestID;
   	try {
	   		Properties properties = System.getProperties();
	   		properties.put("mail.smtps.host", MAIL_SERVER);
	   		properties.put("mail.smtps.auth", "true");
	
	   		Session session = Session.getInstance(properties);
	   		MimeMessage msg = new MimeMessage(session);
	
	   		msg.setFrom(new InternetAddress(fromAddress));
	   		msg.addRecipients(javax.mail.Message.RecipientType.TO, email);
	   		msg.setSubject(subject);
	   		msg.setText(message);
	
	   		Transport tr = session.getTransport("smtps");
	   		tr.connect(MAIL_SERVER, USERNAME, PASSWORD);
	   		tr.sendMessage(msg, msg.getAllRecipients());
	   		tr.close();
   		} catch (AddressException ex) {
   			System.out.println(ex.getMessage());
   		} catch (MessagingException ex) {
   			System.out.println(ex.getMessage());
   		}
   	System.out.println("An email reminder was sent to "+email);
   	// end of sending mail
	}

}
